﻿using System;
using System.Data;
using System.Data.Odbc;
using System.Text.RegularExpressions;
using Models.Extensions;

namespace MySQLProviderODBC
{
    /// <summary>
    /// MySQL_ODBC_Driver.cs, SQL server routines for MySQL ODBC connections
    /// </summary>
    internal class MySQL_ODBC_Driver
    {
        /// <summary>
        /// Make sure the connection string works
        /// </summary>
        /// <param name="errorMsg">Contains any error messages</param>
        /// <returns>True if the connection string is valid</returns>
        public bool CheckConnection(out string errorMsg)
        {
            bool result = true;
            errorMsg = string.Empty;

            OdbcConnection sqlconn = new OdbcConnection(m_ConnectionString);

            try
            {
                // Try to open the connection
                sqlconn.Open();
            }
            catch (Exception ex)
            {
                // If we end up here then something went wrong so return false and an error message
                result = false;
                errorMsg = ex.Message;
            }
            finally
            {
                // MAke sure the connection is closed
                sqlconn.Close();
            }

            return result;
        }

        /// <summary>
        /// ExecuteQuery, execute the supplied SQL Query and return a dataset
        /// </summary>
        /// <param name="sql">The SQL to execute</param>
        /// <param name="errorMsg">Any error messages returned if the query fails</param>
        /// <param name="timeout">The time out value in seconds to wait for the query to finish</param>
        /// <returns>A dataset containing the results on success, otherwise null</returns>
        public DataSet ExecuteQuery(string sql, out string errorMsg, int timeout)
        {
            //Run a query and return the resulting data set

            DataSet results = null;
            errorMsg = string.Empty;

            OdbcConnection sqlConn = new OdbcConnection(m_ConnectionString);

            try
            {
                //Open a conntion to the database and run the supplied query
                sqlConn.Open();

                OdbcCommand sqlCmd = new OdbcCommand(sql, sqlConn);
                sqlCmd.CommandTimeout = timeout;
                OdbcDataAdapter sqlData = new OdbcDataAdapter(sqlCmd);

                results = new DataSet("Results");
                sqlData.Fill(results);
            }
            catch (Exception ex)
            {
                errorMsg = ex.Message;
            }
            finally
            {
                // Ensure the connection is closed
                if (sqlConn.State != ConnectionState.Closed)
                {
                    sqlConn.Close();
                }
            }

            return results;
        }

        /// <summary>
        /// ExecuteSQL, execute the supplied SQL and return whther it was a success or not
        /// </summary>
        /// <param name="sql">The SQL to execute</param>
        /// <param name="errorMsg">Any error messages returned if the query fails</param>
        /// <param name="timeout">The time out value in seconds to wait for the query to finish</param>
        /// <returns>True if successfull, otherwise false</returns>
        public bool ExecuteSql(string sql, out string errorMsg, int timeout)
        {
            //Run a query and return true if it succeeds or false if it fails

            //Create a connection to the database
            OdbcConnection sqlConn = new OdbcConnection(m_ConnectionString);
            bool results;
            errorMsg = string.Empty;

            try
            {
                //Open the connection and run the query
                sqlConn.Open();
                OdbcCommand sqlCmd = new OdbcCommand(sql, sqlConn);
                sqlCmd.CommandTimeout = timeout;
                sqlCmd.ExecuteNonQuery();

                //If we get here the query was successful
                results = true;
            }
            catch (Exception ex)
            {
                //The query failed so we will be returning false and an error message
                errorMsg = ex.Message;
                results = false;
            }
            finally
            {
                //Ensure the connection is closed properly
                if (sqlConn.State != ConnectionState.Closed)
                    sqlConn.Close();
            }

            return results;
        }

        /// <summary>
        /// Execute a batch of sql commands seperated by GO commands
        /// </summary>
        /// <param name="sql">The sql to execute</param>
        /// <param name="errorMsg">Any error messages returned if the query fails</param>
        /// <param name="timeout">The time out value in seconds to wait for the query to finish</param>
        /// <returns>True if successfull, otherwise false</returns>
        public bool ExecuteSqlWithGo(string sql, out string errorMsg, int timeout)
        {
            bool result = true;
            errorMsg = string.Empty;

            // First split the sql up into blocks, each block contains sql ending in a GO command
            Regex rExpr = new Regex(@"^\s*GO\s*$", RegexOptions.IgnoreCase | RegexOptions.Multiline);
            string[] sqlParts = rExpr.Split(sql);

            if (sqlParts.GetUpperBound(0) == 0)
            {
                // No items in the list so this is just a simple sql query with no GO commands.
                // Simply call down to ExecuteSql
                result = ExecuteSql(sql, out errorMsg, timeout);
            }
            else
            {

                // Attempt to connect to the database
                OdbcConnection sqlConn = new OdbcConnection(m_ConnectionString);
                bool connectionSuccess;
                try
                {
                    sqlConn.Open();
                    connectionSuccess = true;
                }
                catch (Exception ex)
                {
                    errorMsg = ex.Message;
                    connectionSuccess = false;
                }

                // If we connected then we can start running our query
                if (connectionSuccess)
                {
                    // Start a new sql transaction
                    OdbcTransaction sqlTran = sqlConn.BeginTransaction();

                    // Create a command to execute
                    OdbcCommand sqlCmd = new OdbcCommand
                                            {
                                                Connection = sqlConn,
                                                CommandTimeout = timeout,
                                                Transaction = sqlTran
                                            };

                    foreach (string sqlPart in sqlParts)
                    {
                        if (sqlPart.Length > 0)
                        {
                            try
                            {
                                sqlCmd.CommandText = sqlPart;
                                sqlCmd.ExecuteNonQuery();
                            }
                            catch (Exception)
                            {
                                // Something has gone wrong so roll back everything we have done so far and stop here
                                sqlTran.Rollback();
                                result = false;
                                break;
                            }
                        }
                    }

                    // If nothing went wrong then commit the transaction
                    if (result)
                    {
                        sqlTran.Commit();
                    }
                }

                // Make sure the connection is closed
                if (sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                }
            }

            return result;
        }

        /// <summary>
        /// CheckDataset, Check to see if the supplied dataset if null and if it isn't,
        /// whether or not there are any rows in it
        /// </summary>
        /// <param name="dsToCheck">The data set to check</param>
        /// <returns>True is the dataset is valid and contains data, otherwise false</returns>
        public static bool CheckDataset(DataSet dsToCheck)
        {
            bool result = false;

            if ((dsToCheck != null) && (dsToCheck.Tables.Count > 0) && (dsToCheck.Tables[0].Rows.Count > 0))
            {
                result = true;
            }
            return result;
        }

        /// <summary>
        /// MySQL_ODBC_Driver, constructor
        /// </summary>
        /// <param name="connString">The connection string to use</param>
        public MySQL_ODBC_Driver(string connString)
        {
            m_ConnectionString = connString;
        }

        /// <summary>
        /// Create an instance of a MySQL_ODBC_Driver using a database object
        /// </summary>
        /// <param name="database">The database to connect to</param>
        public MySQL_ODBC_Driver(Models.Database database)
        {
            if (database != null)
            {
                OdbcConnectionStringBuilder connStrBuilder = new OdbcConnectionStringBuilder();

                // Build the connection string
                connStrBuilder.Driver = database.DatabaseServer.ServerConnection.GetString("Version", string.Empty);
                connStrBuilder.Add("Server", database.DatabaseServer.ServerName);
                connStrBuilder.Add("Database", database.DatabaseName);
                connStrBuilder.Add("User", database.DatabaseServer.ServerConnection.GetString("UserName", string.Empty));
                connStrBuilder.Add("Password", database.DatabaseServer.ServerConnection.GetString("Password", string.Empty));
                connStrBuilder.Add("Port", database.DatabaseServer.ServerConnection.GetString("Port", "3306"));
                connStrBuilder.Add("Option", "3");
                
                // Store the connection string for later use
                m_ConnectionString = connStrBuilder.ConnectionString;
            }
        }

        /// <summary>
        /// m_ConnectionString, the connection string currently being used
        /// </summary>
        private readonly string m_ConnectionString;

    }
}
